﻿/********************************************************
 * 类名： InventoryDAL.cs
 * 功能： 库存数据层
 *   ver	变更日		作者			变更内容
 * ────────────────────────────────────────
 *   V01.0	2014-01-22	李波			初版
 *
 * Copyright (c) 2013，李波版权所有
 ********************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using PSS.Entity.InitPurManage;
using PSS.Utility;

namespace PSS.DAL.CommonManage
{
    public class InventoryDAL : DbHelperBase
    {
        public void AddInventory(List<InventoryEntity> list)
        {
            var conn = Db.GetConnection() as SqlConnection;
            if (conn == null)
            {
                throw new InvalidOperationException("No Valid Database Connection!");
            }
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            using (var bcp = new SqlBulkCopy(conn))
            {
                var mapInventoryId =
                    new SqlBulkCopyColumnMapping("InventoryId", "InventoryId");
                bcp.ColumnMappings.Add(mapInventoryId);

                var mapGoodsId =
                    new SqlBulkCopyColumnMapping("GoodsId", "GoodsId");
                bcp.ColumnMappings.Add(mapGoodsId);

                var mapStorageId =
                    new SqlBulkCopyColumnMapping("StorageId", "StorageId");
                bcp.ColumnMappings.Add(mapStorageId);

                var mapInventoryType =
                   new SqlBulkCopyColumnMapping("InventoryType", "InventoryType");
                bcp.ColumnMappings.Add(mapInventoryType);

                var mapInventoryQty =
                   new SqlBulkCopyColumnMapping("InventoryQty", "InventoryQty");
                bcp.ColumnMappings.Add(mapInventoryQty);

                bcp.DestinationTableName = "PSS_Inventory";

                try
                {
                    bcp.WriteToServer(Collection.ToDataTable(list));
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }
        }

        public IList<InventoryEntity> GetInventoryQtyById(InventoryQcEntity qcmodel)
        {
            var list = new List<InventoryEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT B.StorageCode,B.StorageName,A.InventoryType,A.InventoryQty from PSS_Inventory AS A ");
            strSql.Append(" INNER JOIN PSS_Storage AS B ON A.StorageId=B.StorageId ");
            strSql.Append(" WHERE A.GoodsId=@GoodsId ORDER BY B.CreateTime ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@GoodsId", qcmodel.GoodsId);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new InventoryEntity();
                            model.StorageCode = reader["StorageCode"] == obj ? null : Convert.ToString(reader["StorageCode"]);
                            model.StorageName = reader["StorageName"] == obj ? null : Convert.ToString(reader["StorageName"]);
                            if (reader["InventoryType"] != DBNull.Value)
                                model.InventoryType = Convert.ToInt32(reader["InventoryType"].ToString());
                            if (reader["InventoryQty"] != DBNull.Value)
                                model.InventoryQty = Convert.ToDecimal(reader["InventoryQty"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public List<InventoryEntity> GetInitialQty(InventoryQcEntity qcmodel)
        {
            var list = new List<InventoryEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT B.StorageCode,B.StorageName,A.InitialNumber AS InventoryQty FROM PSS_GoodsDetail AS A ");
            strSql.Append(" INNER JOIN PSS_Storage AS B ON A.StorageId=B.StorageId ");
            strSql.Append(" WHERE GoodsId=@GoodsId  ORDER BY B.CreateTime ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@GoodsId", qcmodel.GoodsId);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new InventoryEntity();
                            model.StorageCode = reader["StorageCode"] == obj ? null : Convert.ToString(reader["StorageCode"]);
                            model.StorageName = reader["StorageName"] == obj ? null : Convert.ToString(reader["StorageName"]);
                            if (reader["InventoryQty"] != DBNull.Value)
                                model.InventoryQty = Convert.ToDecimal(reader["InventoryQty"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public List<InventoryEntity> GetStorage(InventoryQcEntity qcmodel)
        {
            var list = new List<InventoryEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT B.StorageCode,B.StorageName,A.InitialNumber AS InventoryQty FROM PSS_GoodsDetail AS A ");
            strSql.Append(" INNER JOIN PSS_Storage AS B ON A.StorageId=B.StorageId ");
            strSql.Append(" WHERE A.GoodsId=@GoodsId  ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@GoodsId", qcmodel.GoodsId);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new InventoryEntity();
                            model.StorageCode = reader["StorageCode"] == obj ? null : Convert.ToString(reader["StorageCode"]);
                            model.StorageName = reader["StorageName"] == obj ? null : Convert.ToString(reader["StorageName"]);
                            if (reader["InventoryQty"] != DBNull.Value)
                                model.InventoryQty = Convert.ToDecimal(reader["InventoryQty"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public bool DeleteInventory(string id)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [PSS_Inventory] WHERE InventoryId=@InventoryId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@InventoryId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public void ModifyInventory(List<InventoryEntity> ilist)
        {
            var conn = Db.GetConnection() as SqlConnection;
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT InventoryId,GoodsId,StorageId,InventoryType,InventoryQty INTO [#Tmp] FROM PSS_Inventory Where 1=2";
                    cmd.ExecuteNonQuery();
                }

                using (var bcp = new SqlBulkCopy(conn))
                {
                    var mapInventoryId =
                   new SqlBulkCopyColumnMapping("InventoryId", "InventoryId");
                    bcp.ColumnMappings.Add(mapInventoryId);

                    var mapGoodsId =
                        new SqlBulkCopyColumnMapping("GoodsId", "GoodsId");
                    bcp.ColumnMappings.Add(mapGoodsId);

                    var mapStorageId =
                        new SqlBulkCopyColumnMapping("StorageId", "StorageId");
                    bcp.ColumnMappings.Add(mapStorageId);

                    var mapInventoryType =
                       new SqlBulkCopyColumnMapping("InventoryType", "InventoryType");
                    bcp.ColumnMappings.Add(mapInventoryType);

                    var mapInventoryQty =
                      new SqlBulkCopyColumnMapping("InventoryQty", "InventoryQty");
                    bcp.ColumnMappings.Add(mapInventoryQty);

                    bcp.DestinationTableName = "#Tmp";

                    try
                    {
                        bcp.WriteToServer(Collection.ToDataTable(ilist));
                    }
                    catch (Exception ex)
                    {
                        EventsLog.WriteLog("ErrorInfo" + ex.Message);
                    }
                }

                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"Update A Set A.GoodsId=B.GoodsId,A.StorageId=B.StorageId,A.InventoryType=B.InventoryType,A.InventoryQty=B.InventoryQty From #Tmp B Inner Join UT_PrdTask A ON A.InventoryId=B.InventoryId;Drop Table #Tmp;";
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}
